<?

    if (isset($_REQUEST['add_name'])) {
        // Process the request to add a new project
        $name = htmlspecialchars($_REQUEST['add_name']);
        $sql = 'INSERT INTO projects SET userID=:userID, name=:name, created=NOW()';
        $query = $db->prepare($sql);
        $query->execute(array(':userID'=>$user->getID(), ':name'=>$name));
        refreshPage('projects');
    }
    if (isset($_REQUEST['add_folder_name'])) {
        // Verify this is a user's project
        $sql = 'SELECT userID FROM projects WHERE ID=:ID AND userID=:userID';
        $query = $db->prepare($sql);
        $query->execute(array(':ID'=>$_REQUEST['projectID'], ':userID'=>$user->getID()));
        if (count($query->fetchAll()) != 0) {
            $name = htmlspecialchars($_REQUEST['add_folder_name']);
            $sql = 'INSERT INTO project_folders SET projectID=:projectID, name=:name';
            $query = $db->prepare($sql);
            $query->execute(array(':projectID'=>$_REQUEST['projectID'], ':name'=>$name));
        }
        refreshPage('projects', array('projectID'=>$_REQUEST['projectID']));
    }
    if (isset($_REQUEST['add_to_project'])) {
        $split = explode('f', $_REQUEST['add_to_project']);
        $projectID = substr($split[0], 1);
        $folderID = $split[1];
        $sql = 'INSERT INTO project_entries SET projectID=:projectID, folderID=:folderID, entryID=:entryID';
        $query = $db->prepare($sql);
        $query->execute(array(':projectID'=>$projectID, ':folderID'=>$folderID, ':entryID'=>$_REQUEST['entryID']));
        refreshPage('projects', array('projectID'=>$projectID, 'folderID'=>$folderID));
    }
    if (isset($_REQUEST['add_field_name'])) {
        // Verify this is a user's project
        $sql = 'SELECT userID FROM projects WHERE ID=:ID AND userID=:userID';
        $query = $db->prepare($sql);
        $query->execute(array(':ID'=>$_REQUEST['projectID'], ':userID'=>$user->getID()));
        if (count($query->fetchAll()) != 0) {
            $name = htmlspecialchars($_REQUEST['add_field_name']);
            $sql = 'INSERT INTO project_fields SET projectID=:projectID, folderID=:folderID, name=:name';
            $query = $db->prepare($sql);
            $query->execute(array(':projectID'=>$_REQUEST['projectID'], ':folderID'=>$_REQUEST['folderID'], ':name'=>$name));
        }
        refreshPage('projects', array('projectID'=>$_REQUEST['projectID'], 'folderID'=>$_REQUEST['folderID']));
    }
    if (isset($_REQUEST['save_fields'])) {
        // Verify this is a user's project
        $sql = 'SELECT userID FROM projects WHERE ID=:ID AND userID=:userID';
        $query = $db->prepare($sql);
        $query->execute(array(':ID'=>$_REQUEST['projectID'], ':userID'=>$user->getID()));
        if (count($query->fetchAll()) != 0) {
            $sql = 'SELECT * FROM project_fields WHERE projectID=:projectID AND (folderID=:folderID OR folderID=0)';
            $query = $db->prepare($sql);
            $query->execute(array(':projectID'=>$_REQUEST['projectID'], ':folderID'=>$_REQUEST['folderID']));
            $fields = $query->fetchAll();

            $sql = 'REPLACE project_field_data SET projectID=:projectID, folderID=:folderID, fieldID=:fieldID, entryID=:entryID, data=:data';
            $query = $db->prepare($sql);
            foreach ($_REQUEST['field'] as $ID=>$field) {
                $folderID=$_REQUEST['folderID'];
                $data = htmlspecialchars($field);

                // Make project fields global
                foreach ($fields as $f) {
                    if ($f["ID"] == $ID && $f["folderID"] == 0) $folderID = 0;
                }
                $query->execute(array(':projectID'=>$_REQUEST['projectID'], 
                    ':folderID'=>$folderID, 
                    ':fieldID'=>$ID, ':entryID'=>$_REQUEST['entryID'], ':data'=>$data));
            }
        }
        //refreshPage('projects', array('projectID'=>$_REQUEST['projectID'], 'folderID'=>$_REQUEST['folderID']));
    }


    // Load all of the entries in all projects for counting
    $sql = 'SELECT project_entries.* FROM project_entries, projects WHERE projects.userID=:userID AND project_entries.projectID=projects.ID';
    $query = $db->prepare($sql);
    $query->execute(array(':userID'=>$user->getID()));
    $entries = $query->fetchAll();

    // Load all of the user's projects
    $sql = 'SELECT * FROM projects WHERE userID=:userID';
    $query = $db->prepare($sql);
    $query->execute(array(':userID'=>$user->getID()));
    $projects = $query->fetchAll();
    foreach ($projects as $pk=>$pv) {
        $projects[$pk]['count'] = 0;
        $projects[$pk]['count_inc_folders'] = 0;
        foreach ($entries as $ek=>$ev) {
            if ($ev['projectID'] == $pv['ID'] && $ev['folderID'] == 0) $projects[$pk]['count']++;
            if ($ev['projectID'] == $pv['ID']) $projects[$pk]['count_inc_folders']++;
        }
    }
    $smarty->assign('projects', $projects);

    // Determine if we have an active project valid for this user
    $active_project = 0;
    foreach ($projects as $p)
    {
        if (isset($_REQUEST['projectID']) && $p['ID'] == $_REQUEST['projectID']) $active_project = $_REQUEST['projectID'];
    }
    $smarty->assign('active_project', $active_project);

    // Load all of the sub-folders for this project
    $folders = array();
    if ($active_project != 0) {
        $sql = 'SELECT * FROM project_folders WHERE projectID=:projectID';
        $query = $db->prepare($sql);
        $query->execute(array(':projectID'=>$active_project));
        $folders = $query->fetchAll();
        foreach ($folders as $fk=>$fv) {
            $folders[$fk]['count'] = 0;
            foreach ($entries as $ek=>$ev) {
                if ($ev['folderID'] == $fv['ID']) $folders[$fk]['count']++;
            }
        }
    }
    $smarty->assign('folders', $folders);

    // Determine if we have an active folder valid for this user
    $active_folder = 0;
    foreach ($folders as $f)
    {
        if (isset($_REQUEST['folderID']) && $f['ID'] == $_REQUEST['folderID']) $active_folder = $_REQUEST['folderID'];
    }
    $smarty->assign('active_folder', $active_folder);


    // Determine active entries
    $sql = 'SELECT entries.*, project_entries.notes AS project_notes FROM entries, project_entries WHERE entries.ID=project_entries.entryID AND project_entries.projectID=:projectID AND project_entries.folderID=:folderID';
    $query = $db->prepare($sql);
    $query->execute(array(':projectID'=>$active_project, ':folderID'=>$active_folder));
    $results = $query->fetchAll();


    // Determine the field and project fields
    $sql = 'SELECT * FROM project_fields WHERE projectID=:projectID AND (folderID=:folderID OR folderID=0)';
    $query = $db->prepare($sql);
    $query->execute(array(':projectID'=>$active_project, ':folderID'=>$active_folder));
    $fields = $query->fetchAll();
    $sql = 'SELECT * FROM project_field_data WHERE projectID=:projectID AND (folderID=:folderID OR folderID=0)';
    $query = $db->prepare($sql);
    $query->execute(array(':projectID'=>$active_project, ':folderID'=>$active_folder));
    $field_data = $query->fetchAll();

    // Put the field data into entries
    foreach($results as $k=>$v) {
        $results[$k]["fields"] = '';
        foreach ($field_data as $dk=>$dv) {
            if ($dv['entryID'] == $v['ID']) {
                $results[$k]["fields"][$dv["fieldID"]] = $dv["data"];
            }
        }
    } 

    $smarty->assign('fields', $fields);
    $smarty->assign('field_data', $field_data);
    $smarty->assign('results', $results);
?>
